﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XQ.WeChatOfficialAccount.DAL.Model.Analysis;
using XQ.WeChatOfficialAccount.DAL.Models;

namespace XQ.WeChatOfficialAccount.DAL.DAL
{
    /// <summary>
    /// 统计分析数据操作类
    /// </summary>
    public class AnalysisDAL
    {
        RoleDal roleDal = new RoleDal();

        /// <summary>
        /// 获取性别分析数据
        /// </summary>
        /// <returns></returns>
        public SexAnalysisModel GetSexAnalysis(TokenModel token)
        {
            using (WeChatOfficialAccountEntities se = new WeChatOfficialAccountEntities())
            {
                //区分分校
                string sqlPart = "";
                if (roleDal.IsDepartmentRole(token.RoleId))
                {
                    sqlPart = " schoolArea=" + token.DepartmentID + " and";
                }
                StringBuilder sql = new StringBuilder();
                sql.Append(" select ");
                sql.Append(" (select count(1) from studentinfo where " + sqlPart + " sex=1) as man ");
                sql.Append(" , ");
                sql.Append(" (select count(1) from studentinfo where " + sqlPart + " sex=0) as woman ");
                return se.Database.SqlQuery<SexAnalysisModel>(sql.ToString()).FirstOrDefault();
            }
        }

        /// <summary>
        /// 获取区域分析数据(areaType：1-全国、2-全省、3-全市)
        /// </summary>
        /// <returns></returns>
        public List<AreaAnalysisModel> GetAreaAnalysis(int areaType, TokenModel token)
        {
            using (WeChatOfficialAccountEntities se = new WeChatOfficialAccountEntities())
            {
                //区分分校
                string sqlPart = "";
                if (roleDal.IsDepartmentRole(token.RoleId))
                {
                    sqlPart = " schoolArea=" + token.DepartmentID + " and";
                }
                StringBuilder sql = new StringBuilder();
                sql.Append(" select count(1) as [count] , ");
                sql.Append(" (select areaName from Sys_area where id=province) areaName ");
                sql.Append(" from studentInfo s where " + sqlPart + " (select pid from sys_area where id=s.province)=0 ");
                sql.Append(" group by province ");
                sql.Append(" union ");
                sql.Append(" select count(1) as [count] ,  ");
                sql.Append("  '其它' areaName ");
                sql.Append("  from studentInfo s where " + sqlPart + " (select pid from sys_area where id=s.province) is null ");
                sql.Append("  group by province ");
                if (areaType == 2)
                {
                    sql.Clear();
                    sql.Append("  select count(1) as [count] ,  ");
                    sql.Append("  (select areaName from Sys_area where id=city) areaName ");
                    sql.Append("  from studentInfo s ");
                    sql.Append("  where " + sqlPart + "  (select pid from sys_area where id=s.city)=32 ");
                    sql.Append("  group by s.city ");
                    sql.Append("  union ");
                    sql.Append("  select count(1) as [count] , '其它' areaName ");
                    sql.Append("  from studentInfo s ");
                    sql.Append("  where " + sqlPart + "  (select pid from sys_area where id=s.city)<>32 or s.city is null ");
                }
                if (areaType == 3)
                {
                    sql.Clear();
                    sql.Append("  select count(1) as [count] ,  ");
                    sql.Append("  (select areaName from Sys_area where id=county) areaName ");
                    sql.Append("  from studentInfo s ");
                    sql.Append("  where  " + sqlPart + " (select pid from sys_area where id=s.county)=320200000000 ");
                    sql.Append("  group by s.county ");
                    sql.Append("  union ");
                    sql.Append("  select count(1) as [count] , '其它' areaName ");
                    sql.Append("  from studentInfo s ");
                    sql.Append("  where " + sqlPart + "  (select pid from sys_area where id=s.county)<>320200000000 or s.county is null ");
                }

                return se.Database.SqlQuery<AreaAnalysisModel>(sql.ToString()).ToList();
            }
        }

        /// <summary>
        /// 获取趋势分析数据
        /// </summary>
        /// <param name="year">年份（默认当年）</param>
        /// <returns></returns>
        public List<KeyValueModel> GetTrendAnalysis(int schoolArea, int year, TokenModel token)
        {
            using (WeChatOfficialAccountEntities se = new WeChatOfficialAccountEntities())
            {
                //区分分校
                string sqlPart = "";
                if (roleDal.IsDepartmentRole(token.RoleId))
                {
                    sqlPart = " schoolArea=" + token.DepartmentID + " and ";
                }
                string sqlPartAdmin = "";
                //管理员选择校区
                if (schoolArea > 0)
                {
                    sqlPartAdmin = " schoolArea=" + schoolArea + "and ";
                }
                List<SqlParameter> sqlParameters = new List<SqlParameter>();
                if (year > 2000)
                {
                    sqlParameters.Add(new SqlParameter("@year", year));
                }
                else
                {
                    sqlParameters.Add(new SqlParameter("@year", DateTime.Now.Year));
                }

                StringBuilder sql = new StringBuilder();
                sql.Append(" select sum(value) as value,Name");
                sql.Append(" from");
                sql.Append(" (");
                sql.Append(" select count(1) as Value,cast(Month(entranceDate) as varchar) as Name");
                sql.Append(" from StudentInfo where " + sqlPart + sqlPartAdmin+" Year(entranceDate)=@year");
                sql.Append(" group by Month(entranceDate)");
                sql.Append(" union");
                sql.Append(" select 0 as Value, '1' as Name");
                sql.Append(" union");
                sql.Append(" select 0 as Value, '2'as Name");
                sql.Append(" union");
                sql.Append(" select 0 as Value, '3' as Name");
                sql.Append(" union");
                sql.Append(" select 0 as Value, '4' as Name");
                sql.Append(" union");
                sql.Append(" select 0 as Value, '5' as Name");
                sql.Append(" union");
                sql.Append(" select 0 as Value, '6' as Name");
                sql.Append(" union");
                sql.Append(" select 0 as Value, '7' as Name");
                sql.Append(" union");
                sql.Append(" select 0 as Value, '8' as Name");
                sql.Append(" union");
                sql.Append(" select 0 as Value, '9' as Name");
                sql.Append(" union");
                sql.Append(" select 0 as Value, '10' as Name");
                sql.Append(" union");
                sql.Append(" select 0 as Value, '11' as Name");
                sql.Append(" union");
                sql.Append(" select 0 as Value, '12' as Name");
                sql.Append(" ) t1");
                sql.Append(" group by Name");

                return se.Database.SqlQuery<KeyValueModel>(sql.ToString(), sqlParameters.ToArray()).ToList();
            }
        }

        /// <summary>
        /// 诊断类别分析
        /// </summary>
        /// <returns></returns>
        public List<DiagnosisTypeAnalysisModel> GetDiagnosisTypeAnalysis(TokenModel token)
        {
            using (WeChatOfficialAccountEntities se = new WeChatOfficialAccountEntities())
            {                
                //区分分校
                string sqlPart = "";
                if (roleDal.IsDepartmentRole(token.RoleId))
                {
                    sqlPart = " schoolArea=" + token.DepartmentID + " and";
                }
                StringBuilder sql = new StringBuilder();
                sql.Append(" select count(1) as [count],");
                sql.Append(" (select title from sys_dics where id= d.diagnosisType) as diagnosisTypeName  ");
                sql.Append(" from StudentInfo s");
                sql.Append(" inner join Diagnosis d on s.id=d.studentid");
                sql.Append(" inner join Sys_dics dict on d.diagnosisType=dict.id");
                sql.Append(" where " + sqlPart + " dict.Code is not null");
                sql.Append(" group by d.diagnosisType ");
                sql.Append(" union ");
                sql.Append(" select count(1) as [count],");
                sql.Append(" '广泛性发育障碍其他类别' as diagnosisTypeName  ");
                sql.Append(" from StudentInfo s");
                sql.Append(" inner join Diagnosis d on s.id=d.studentid");
                sql.Append(" inner join Sys_dics dict on d.diagnosisType=dict.id");
                sql.Append(" where " + sqlPart + " dict.Code is null");
                sql.Append(" group by d.diagnosisType ");
                return se.Database.SqlQuery<DiagnosisTypeAnalysisModel>(sql.ToString()).ToList();
            }
        }

        /// <summary>
        /// 自费和补助分析
        /// </summary>
        /// <returns></returns>
        public DeclareSubsidyAnalysisModel GetDeclareSubsidyAnalysis(TokenModel token)
        {
            using (WeChatOfficialAccountEntities se = new WeChatOfficialAccountEntities())
            {
                //区分分校
                string sqlPart = "";
                if (roleDal.IsDepartmentRole(token.RoleId))
                {
                    sqlPart = " schoolArea=" + token.DepartmentID + " and";
                }
                StringBuilder sql = new StringBuilder();
                sql.Append(" select ");
                sql.Append(" (select count(1) from studentinfo where " + sqlPart + " isDeclare=1) as DeclareSubsidy ");
                sql.Append(" , ");
                sql.Append(" (select count(1) from studentinfo where " + sqlPart + " (isDeclare<>1 or isDeclare is null)) as NoDeclareSubsidy ");
                return se.Database.SqlQuery<DeclareSubsidyAnalysisModel>(sql.ToString()).FirstOrDefault();
            }
        }
    }
}
